package com.wanmait.shoppingsys.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.wanmait.shoppingsys.util.DbHelper;
import com.wanmait.shoppingsys.util.ManagePager;
import com.wanmait.shoppingsys.vo.City;
import com.wanmait.shoppingsys.vo.Evaluation;
import com.wanmait.shoppingsys.vo.Goods;
import com.wanmait.shoppingsys.vo.Specialty;
import com.wanmait.shoppingsys.vo.Type;
import com.wanmait.shoppingsys.vo.UserInfo;

public class ManageDAO {
	
	//删除

	public int getDataCount1() {
		int count=0;
		String sql="select count(*) from specialty";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
 
	public ManagePager findAll1(ManagePager managepager){
		ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		managepager.setData(specialtys);
		managepager.setRowCount(this.getDataCount1());
		
		String sql = " SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id LIMIT ?,?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
			ps.setInt(2, managepager.getPageSize());
			rs = ps.executeQuery();
			
			while(rs.next()) {
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return managepager;
	}
	

	/*public int getDataCount2() 
	{
		int count = 0;
		String sql = "select count(*) from specialty";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
	public ManagePager findAllProduce( ManagePager managepager) //查询出来数据库所有物品
	{
		ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		managepager.setData(specialtys);
		managepager.setRowCount(this.getDataCount2());
		String sql = "SELECT * FROM specialty LIMIT ?,?";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
			ps.setInt(2, managepager.getPageSize());
			rs = ps.executeQuery();
			while(rs.next()) {
				Specialty specialty = new Specialty();
				specialty.setId(rs.getInt("id"));
				specialty.setName(rs.getString("name"));
				specialty.setPrice(rs.getDouble("price"));
				specialtys.add(specialty);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return managepager;
	}*/
	public int getDataCount1(Integer specialtyId) {
		int count=0;
		String sql="select count(*) from specialty";
		Connection conn = DbHelper.getConn();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1,specialtyId);
			rs = ps.executeQuery();
			if(rs.next()) {
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return count;
	}
 
	
  public void delete(Integer id) {
		String sql = "DELETE FROM  specialty WHERE id=?";
		ArrayList params = new ArrayList();
		params.add(id);
		DbHelper.update(sql,params);
		 
	}
   
   
   
   
   
   
   
	  
	    //评论
	    
	    public int getDataCount3() {
			int count=0;
			String sql="select count(*) from evaluation";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}


		public ManagePager findAll3(ManagePager managepager){
			ArrayList<Evaluation> evaluations = new ArrayList<Evaluation>();
			managepager.setData(evaluations);
			managepager.setRowCount(this.getDataCount3());
			
			String sql = "SELECT evaluation.*, userinfo.name as user_name,specialty.name as specialty_name FROM evaluation " + 
					"left join userinfo on userinfo.id=evaluation.user_id " + 
					"left join specialty on specialty.id=evaluation.specialty_id LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Evaluation evaluation = new Evaluation();
					
					evaluation.setId(rs.getInt("id"));
					evaluation.setTime(rs.getTimestamp("time"));
					evaluation.setContent(rs.getString("content"));
					
					
					UserInfo userinfo=new UserInfo();
					userinfo.setName(rs.getString("user_name"));
					
					
					
					Specialty specialty=new Specialty();
					specialty.setName(rs.getString("specialty_name"));
					
					evaluation.setSpecialty(specialty);
					evaluation.setUesrinfo(userinfo);
					evaluations.add(evaluation);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		
		
		
		
		
		
		
		//订单
		
		public int getDataCount4() {
			int count=0;
			String sql="select count(*) from goods_order";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}


		public ManagePager findAll4(ManagePager managepager){
			ArrayList<Goods> goods = new ArrayList<Goods>();
			managepager.setData(goods);
			managepager.setRowCount(this.getDataCount4());
			
			String sql = "SELECT * FROM goods_order LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Goods good = new Goods();
					good.setId(rs.getInt("id"));
					good.setTotalPrice(rs.getInt("total_price"));
				    good.setPhone(rs.getString("phone"));
				    good.setPayTime(rs.getTimestamp("pay_time"));
				    good.setAddress(rs.getString("address"));
				    good.setIncomeName(rs.getString("income_name"));
				    good.setOrderNum(rs.getString("order_num"));
				    good.setOrderState(rs.getString("order_state"));
					goods.add(good);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		
		
		
		
		
		
		
		
		
		
		//用户
		
		public int getDataCount5() {
			int count=0;
			String sql="select count(*) from userinfo";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}


		public ManagePager findAll5(ManagePager managepager){
			ArrayList<UserInfo> userinfos = new ArrayList<UserInfo>();
			managepager.setData(userinfos);
			managepager.setRowCount(this.getDataCount5());
			
			String sql = "SELECT * FROM userinfo LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					UserInfo userinfo = new UserInfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setName(rs.getString("name"));
					//userinfo.setAddress(rs.getString("address"));
					userinfo.setEnable(rs.getBoolean("enable"));
					userinfo.setEmail(rs.getString("email"));
					userinfo.setGender(rs.getString("gender"));
					userinfo.setRegTime(rs.getTimestamp("reg_time"));
					userinfos.add(userinfo);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}

		
		
		
		
		
		
		
		
		
		
		
		
		
		//更改
		
		public int getDataCount6() {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}


		public ManagePager findAll6(ManagePager managepager){
			ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
		
			managepager.setData(specialtys);
			
		
			managepager.setRowCount(this.getDataCount6());
			
			String sql = "SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));
					specialty.setPrice(rs.getDouble("price"));
					specialty.setInventory(rs.getInt("inventory"));
					specialty.setPutawayTime(rs.getTimestamp("putaway_time"));
					specialty.setIntroduce(rs.getString("introduce"));
					
					
					Type type=new Type();
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					specialtys.add(specialty);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		public int getDataCount(String name) {     //关键字查找商品个数
			int count=0;
			String sql="select count(*) from specialty where specialty.name like ?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+name+"%");
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
			
		}
		
		public ManagePager findByNote(ManagePager managepager){  //关键字查找商品
			ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
			managepager.setData(specialtys);
			managepager.setRowCount(this.getDataCount(managepager.getGoodsNum()));
			
			String sql = "SELECT specialty.*, type.kind as type_kind,city.name as city_name FROM specialty left join type on type.id=specialty.type_id left join city on city.id=specialty.city_id where specialty.name like ? LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+managepager.getGoodsNum()+"%");
				ps.setInt(2, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(3, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));
					specialty.setPrice(rs.getDouble("price"));
					specialty.setEnable(rs.getBoolean("enable"));
					
					City city=new City();
					city.setId(rs.getInt("city_id"));
					city.setName(rs.getString("city_name"));
					specialty.setCity(city);
					
					Type type=new Type();
					type.setId(rs.getInt("type_id"));
					type.setKind(rs.getString("type_kind"));
					specialty.setType(type);
					
					specialtys.add(specialty);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		public int getDataCount8(String name) {     //关键字查找商品个数
			int count=0;
			String sql="select count(*) from userinfo where name like ?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+name+"%");
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
		
		public ManagePager findByName(ManagePager managepager){
			ArrayList<UserInfo> userinfos = new ArrayList<UserInfo>();
			managepager.setData(userinfos);
			managepager.setRowCount(this.getDataCount8(managepager.getGoodsNum()));
			
			String sql = "SELECT * FROM userinfo where name like ? LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+managepager.getGoodsNum()+"%");
				ps.setInt(2, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(3, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					UserInfo userinfo = new UserInfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setName(rs.getString("name"));
					userinfo.setEmail(rs.getString("email"));
					userinfo.setGender(rs.getString("gender"));
					userinfo.setRegTime(rs.getTimestamp("reg_time"));
					userinfos.add(userinfo);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		public int getDataCount7(Integer specialtyId) {
			int count=0;
			String sql="select count(*) from specialty where id=?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,specialtyId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
		
		
		
		public ManagePager findAllUpdate(ManagePager managepager){
			ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
			managepager.setData(specialtys);
			managepager.setRowCount(this.getDataCount1(managepager.getSpecialtyId()));
			
			String sql = "SELECT * FROM specialty LIMIT ?,?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, managepager.getSpecialtyId());
				ps.setInt(2, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(3, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));
					specialty.setPrice(rs.getDouble("price"));
					specialty.setInventory(rs.getInt("inventory"));
					specialty.setPutawayTime(rs.getTimestamp("putaway_time"));
					specialty.setIntroduce(rs.getString("introduce"));
					
					
					Type type=new Type();
					type.setKind(rs.getString("type_kind"));
					
					specialty.setType(type);
					specialtys.add(specialty);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		
		public int getDataCount2(Integer specialtyId) {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,specialtyId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		
	   public void update(ManagePager managepager) {
			String sql = "UPDATE specialty SET name=?,type=?,price=?,inventory=?,putaway_time=? where id=?";
			Specialty specialty=new Specialty();
			ArrayList params = new ArrayList();
			params.add(specialty.getName());
			params.add(specialty.getType());
			params.add(specialty.getPrice());//類型
			params.add(specialty.getInventory());
			params.add(specialty.getPutawayTime());
			DbHelper.update(sql,params);
		}

	

	
	   
	   
	   //删除订单
	   public int getDataCount8() {
			int count=0;
			String sql="select count(*) from goods_order";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		public ManagePager findAll8(ManagePager managepager){
			ArrayList<Goods> goods = new ArrayList<Goods>();
			managepager.setData(goods);
			managepager.setRowCount(this.getDataCount8());
			
			String sql = "SELECT * FROM goods_order LIMIT ?,? ";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Goods good = new Goods();
					good.setId(rs.getInt("id"));
					good.setOrderNum(rs.getString("order_num"));
					good.setIncomeName(rs.getString("income_name"));
					good.setPhone(rs.getString("phone"));
				    good.setTotalPrice(rs.getDouble("total_price"));
				    good.setOrderState(rs.getString("order_state"));
					goods.add(good);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
		
		
		public int getDataCount8(Integer goodsId) {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,goodsId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		
	   public void deleteGoods(Integer id) {
			String sql = "DELETE FROM  goods_order WHERE id=?";
			ArrayList params = new ArrayList();
			params.add(id);
			DbHelper.update(sql,params);
			 
		}
	   
	   
	   
	   //删除用户信息
	   public int getDataCount9() {
			int count=0;
			String sql="select count(*) from userinfo";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		public ManagePager findAll9(ManagePager managepager){
			ArrayList<UserInfo> userinfos = new ArrayList<UserInfo>();
			managepager.setData(userinfos);
			managepager.setRowCount(this.getDataCount9());
			
			String sql = "SELECT * FROM userinfo LIMIT ?,? ";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					UserInfo userinfo = new UserInfo();
					userinfo.setId(rs.getInt("id"));
					userinfo.setName(rs.getString("name"));
					userinfo.setEmail(rs.getString("email"));
					userinfo.setRegTime(rs.getTimestamp("reg_time"));
					userinfos.add(userinfo);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
	   
		
		
		
		public int getDataCount9(Integer userinfoId) {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,userinfoId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		
	   public void deleteUserinfo(Integer id) {
			String sql = "DELETE FROM userinfo WHERE id=?";
			ArrayList params = new ArrayList();
			params.add(id);
			DbHelper.update(sql,params);
	   }
			 

	   
	   
	   
	   
	   
	   
	   
	 /*  //删除商品信息
	   public int getDataCount10() {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		public ManagePager findAll10(ManagePager managepager){
			ArrayList<Specialty> specialtys = new ArrayList<Specialty>();
			managepager.setData(specialtys);
			managepager.setRowCount(this.getDataCount10());
			
			String sql = "SELECT * FROM specialty LIMIT ?,? ";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1, (managepager.getPageNum()-1)*managepager.getPageSize());
				ps.setInt(2, managepager.getPageSize());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Specialty specialty = new Specialty();
					specialty.setId(rs.getInt("id"));
					specialty.setName(rs.getString("name"));
					specialty.setPrice(rs.getDouble("price"));
					
					City city=new City();
					city.setName(rs.getString("city_name"));
				
					specialty.setCity(city);
					specialtys.add(specialty);
					
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
	   
		public int getDataCount10(Integer specialtyId) {
			int count=0;
			String sql="select count(*) from specialty";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				ps = conn.prepareStatement(sql);
				ps.setInt(1,specialtyId);
				rs = ps.executeQuery();
				if(rs.next()) {
					count = rs.getInt(1);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return count;
		}
	 
		
	   public void delete(ManagePager managepager) {
			String sql = "DELETE FROM specialty WHERE id=?";
			ArrayList params = new ArrayList();
			params.add(managepager.getSpecialtyId());
			DbHelper.update(sql,params);
	   }*/
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
	   
		public ManagePager findByNum(ManagePager managepager) {           //通过订单号查询订单
			ArrayList<Goods> goods = new ArrayList<Goods>();
			managepager.setData(goods);
			
			String sql = "SELECT * FROM goods_order where order_num = ?";
			Connection conn = DbHelper.getConn();
			PreparedStatement ps = null;
			ResultSet rs = null;
			
			try {
				ps = conn.prepareStatement(sql);
				ps.setString(1,managepager.getGoodsNum());
				rs = ps.executeQuery();
				
				while(rs.next()) {
					Goods good = new Goods();
					good.setId(rs.getInt("id"));
					good.setTotalPrice(rs.getInt("total_price"));
				    good.setPhone(rs.getString("phone"));
				    good.setPayTime(rs.getTimestamp("pay_time"));
				    good.setAddress(rs.getString("address"));
				    good.setIncomeName(rs.getString("income_name"));
				    good.setOrderNum(rs.getString("order_num"));
				    good.setOrderState(rs.getString("order_state"));
					goods.add(good);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally {
				DbHelper.close(rs, ps, conn);
			}
			return managepager;
		}
}
